package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.shop.ADDRESS;
import org.apache.taglibs.standard.extra.spath.Step;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ADDRESSDao {
    public static ArrayList<ADDRESS> selectByid(String id){
        ArrayList<ADDRESS> list = new ArrayList<ADDRESS>();
        //设置结果集
        ResultSet rs =null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "select * from lmonkey_address where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                ADDRESS a = new ADDRESS(
                        rs.getString("user_id"),
                        rs.getString("user_address"),
                        rs.getString("user_mobile"),
                        rs.getString("user_name"),
                        rs.getInt("stat"),
                        rs.getInt("id")
                );
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public static int add(String name,String id,String mb,String ar){
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "insert lmonkey_address values (?,?,?,?,?,?)";
            ps= conn.prepareStatement(sql);
            ps.setObject(1,id);
            ps.setObject(2,name);
            ps.setObject(3,mb);
            ps.setObject(4,ar);
            ps.setInt(5,0);
            ps.setInt(6,0);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    public static ArrayList<ADDRESS> selectByid1(String id){
        ArrayList<ADDRESS> list = new ArrayList<ADDRESS>();
        //设置结果集
        ResultSet rs =null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "select * from lmonkey_address where user_id = ? and stat = 0";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                ADDRESS a = new ADDRESS(
                        rs.getString("user_id"),
                        rs.getString("user_address"),
                        rs.getString("user_mobile"),
                        rs.getString("user_name"),
                        rs.getInt("stat"),
                        rs.getInt("id")
                );
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public static ADDRESS selectByid2(String id){
        ADDRESS ds = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_address where user_id = ? and stat = 1";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                ds = new ADDRESS(
                        rs.getString("user_id"),
                        rs.getString("user_address"),
                        rs.getString("user_mobile"),
                        rs.getString("user_name"),
                        rs.getInt("stat"),
                        rs.getInt("id")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ds;
    }

    public static ADDRESS selectByid3(String id){
        ADDRESS ds = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_address where id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                ds = new ADDRESS(
                        rs.getString("user_id"),
                        rs.getString("user_address"),
                        rs.getString("user_mobile"),
                        rs.getString("user_name"),
                        rs.getInt("stat"),
                        rs.getInt("id")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ds;
    }

    public static int updateMoren(String name,String name1,String id){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_address set stat = ? where user_name = ? and user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,1);
            ps.setObject(2,name);
            ps.setObject(3,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            String sql = "update lmonkey_address set stat = ? where user_name = ? and user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,0);
            ps.setObject(2,name1);
            ps.setObject(3,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int delete(int id){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "delete from lmonkey_address where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    public static ADDRESS selectBy(String name){
        ADDRESS ds = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_address where user_name = ? ";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                ds = new ADDRESS(
                        rs.getString("user_id"),
                        rs.getString("user_address"),
                        rs.getString("user_mobile"),
                        rs.getString("user_name"),
                        rs.getInt("stat"),
                        rs.getInt("id")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ds;
    }
}
